package com.view;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.swing.JOptionPane;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.dao.DBUtil;

public class ExcelDate {
	private POIFSFileSystem fs;
	private Workbook wb;
	private Sheet sheet;
	private Row row;

	public Object[][] readExcelContent(FileInputStream is, String fileType) throws IOException {
		if (fileType.equals("xls")) {
			fs = new POIFSFileSystem(is);
			wb = new HSSFWorkbook(fs);
			sheet = (HSSFSheet) wb.getSheetAt(0);
		} else {
			//使用XSSFWorkbook时必须加入xmlbeans-2.6.0.jar包
			wb = new XSSFWorkbook(is);
			sheet = wb.getSheetAt(0);
		}
        
		//获取行条数
		int rowNum = sheet.getLastRowNum() + 1;
		row=sheet.getRow(0);
		//获取列条数
		int rowColNum = row.getPhysicalNumberOfCells();
		//创建二位数组存储单元格数据
		Object[][] strArray = new Object[rowNum][rowColNum];
		System.out.println(rowNum + "," + rowColNum);
		for (int i = 1; i < rowNum; i++) {
			Object str = null;
			row = sheet.getRow(i);
			for (int j = 0; j < rowColNum; j++) {
				str = (Object) getCellFormatValue(row.getCell((short) j));
				strArray[i][j] = str;
			}
		}
		return strArray;
	}

	public Object getCellFormatValue(Cell cell) {
		Object cellValue = "";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			// 如果当前Cell的Type为NUMERIC
			case HSSFCell.CELL_TYPE_NUMERIC:
				// 判断当前的cell是否为Date
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// 如果是Date类型则，取得该Cell的Date值
					Date date = cell.getDateCellValue();
					// 把Date转换成本地格式的字符串
					cellValue = sdf.format(date);
				} else {// 如果是纯数字
					// 取得当前Cell的数值
					Integer num = new Integer((int) cell.getNumericCellValue());
					cellValue = num;
				}
				break;

			// 如果当前Cell的Type为STRING
			case HSSFCell.CELL_TYPE_STRING:
				// 取得当前的Cell字符串
				cellValue = cell.getRichStringCellValue().getString();
				break;
			}
			
			//..还有blank，boolean，error等类型可以判断
			
		} else {
			cellValue = null;
		}
		return cellValue;
	}

	public void insert(String path) throws IOException, SQLException {
		//判断传进来的是什么文件类型，如果xls或者xlsx则执行，其他类型的文件则返回
		String fileType = path.substring(path.indexOf(".") + 1, path.length()).trim();
		System.out.println(fileType);
		if (path == null || path.equals("")) {
			JOptionPane.showMessageDialog(null, "请选择要提交的文件！", "警告", JOptionPane.WARNING_MESSAGE);
			return;
		} else if (fileType.equals("xls") || fileType.equals("xlsx")) {
			Connection con = null;
			File file = new File(path);
			FileInputStream is = new FileInputStream(file);

			Object[][] map = readExcelContent(is, fileType);
			
			for (int i = 1; i < map.length; i++) {
				StringBuffer sb = new StringBuffer("insert into student values (");
				for (int j = 0; j < map[i].length; j++) {
					if (map[i][j] == null || map[i][j].equals("")) {

					} else {
						if (map[i][j].getClass().getSimpleName().equals("String")) {
							sb.append("'");
							sb.append(map[i][j]);
							sb.append("',");
						} else {
							sb.append(map[i][j]);
							sb.append(",");
						}
					}
				}
				String stri = sb.toString().substring(0, sb.length() - 1);
				stri += ")";
				con = DBUtil.getCon();
				Statement st = con.createStatement();
				st.execute(stri);
				st.close();
			}
			is.close();
		} else {
			JOptionPane.showMessageDialog(null, "请选择Excel文件！", "警告", JOptionPane.WARNING_MESSAGE);
			return;
		}
	}
}
